"""refactoring_todos

Revision ID: 0c494b0934df
Revises: 8cd1d967d000
Create Date: 2022-06-24 16:08:20.475425

"""
from alembic import op
import sqlalchemy as sa

# revision identifiers, used by Alembic.
revision = "0c494b0934df"
down_revision = "8cd1d967d000"

filling_assignee_id_query = """
update content_revisions
set assignee_id = (select assignee_id from todo where content_id = content_revisions.content_id)
"""

filling_todo_table = """
insert into todo (content_id, assignee_id, created)
select cr.content_id, cr.assignee_id, cr.created
from content_revisions as cr, content as c
where cr.content_id = c.id and cr.type = 'todo'
"""


def upgrade():
    # ### commands auto generated by Alembic - please adjust! ###
    with op.batch_alter_table("content_revisions") as batch_op:
        batch_op.add_column(
            sa.Column(
                "assignee_id",
                sa.Integer(),
                nullable=True,
            ),
        )
        batch_op.create_foreign_key(
            batch_op.f("fk_content_revisions_assignee_id_users"),
            referent_table="users",
            local_cols=["assignee_id"],
            remote_cols=["user_id"],
            ondelete="RESTRICT",
        )

    op.create_index(
        "idx__content_revisions__assignee_id",
        "content_revisions",
        ["assignee_id"],
        unique=False,
    )

    connection = op.get_bind()
    connection.execute(filling_assignee_id_query)

    op.drop_table("todo")

    if op.get_context().dialect.name == "postgresql":
        op.execute("DROP SEQUENCE seq__todo__todo_id ;")
    # ### end Alembic commands ###


def downgrade():
    # ### commands auto generated by Alembic - please adjust! ###
    if op.get_context().dialect.name == "postgresql":
        op.execute("CREATE SEQUENCE seq__todo__todo_id ;")

    op.create_table(
        "todo",
        sa.Column("todo_id", sa.Integer(), autoincrement=True, nullable=False),
        sa.Column("content_id", sa.Integer(), nullable=False),
        sa.Column("assignee_id", sa.Integer(), nullable=False),
        sa.Column("created", sa.DateTime(), nullable=False),
        sa.ForeignKeyConstraint(
            ["assignee_id"], ["users.user_id"], name="fk_todo_assignee_id_users"
        ),
        sa.ForeignKeyConstraint(
            ["content_id"],
            ["content.id"],
            name="fk_todo_content_id_content",
            onupdate="CASCADE",
            ondelete="CASCADE",
        ),
        sa.PrimaryKeyConstraint("todo_id", name="pk_todo"),
    )

    connection = op.get_bind()
    connection.execute(filling_todo_table)

    op.drop_index("idx__content_revisions__assignee_id", table_name="content_revisions")

    with op.batch_alter_table("content_revisions") as batch_op:
        batch_op.drop_constraint("fk_content_revisions_assignee_id_users", type_="foreignkey")
        batch_op.drop_column("assignee_id")
    # ### end Alembic commands ###
